﻿Imports System.Data.OleDb

Public Class Quiz_QuestionManagement
    Inherits ConnectionClass

    Public Function SelectNumberOfQuestionsPerQuiz(ByVal quizId As Int32) As Int32
        Try

        
        myConnection.Open()
        Dim Cmd As OleDbCommand = New OleDbCommand("SELECT  COUNT(Quiz_QuestionId) AS NumberOfQuestionsPerQuiz FROM  Quizes_Questions WHERE  (QuizId = @f1) GROUP BY QuizId", myConnection)
        Cmd.Parameters.AddWithValue("@f1", quizId)
        Dim dr = Cmd.ExecuteReader()
        dr.Read()
        Dim lastId = 0
        If (dr.HasRows) Then
            lastId = dr.GetValue(0)
        End If
        myConnection.Close()
            Return lastId
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Sub InsertQuizQuestion(ByVal QuizId As Int32, ByVal Quiz_QuestionContent As String, ByVal Quiz_QuestionPossibleAnswer1 As String, ByVal Quiz_QuestionPossibleAnswer2 As String, ByVal Quiz_QuestionPossibleAnswer3 As String, ByVal Quiz_QuestionPossibleAnswer4 As String, ByVal Quiz_QuestionCorrectAnswer As String, ByVal Quiz_QuestionPictureUrl As String, ByVal Quiz_QuestionAnswerDescription As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "INSERT INTO Quizes_Questions (QuizId, Quiz_QuestionContent, Quiz_QuestionPossibleAnswer1, Quiz_QuestionPossibleAnswer2, Quiz_QuestionPossibleAnswer3, Quiz_QuestionPossibleAnswer4, Quiz_QuestionCorrectAnswer, Quiz_QuestionPictureUrl, Quiz_QuestionAnswerDescription) VALUES (@f1,@f2,@f3,@f4,@f5,@f6,@f7,@f8,@f9)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", QuizId)
            newCmd.Parameters.AddWithValue("@f2", Quiz_QuestionContent)
            newCmd.Parameters.AddWithValue("@f3", Quiz_QuestionPossibleAnswer1)
            newCmd.Parameters.AddWithValue("@f4", Quiz_QuestionPossibleAnswer2)
            newCmd.Parameters.AddWithValue("@f5", Quiz_QuestionPossibleAnswer3)
            newCmd.Parameters.AddWithValue("@f6", Quiz_QuestionPossibleAnswer4)
            newCmd.Parameters.AddWithValue("@f7", Quiz_QuestionCorrectAnswer)
            newCmd.Parameters.AddWithValue("@f8", Quiz_QuestionPictureUrl)
            newCmd.Parameters.AddWithValue("@f9", Quiz_QuestionAnswerDescription)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectQuiz_QuestionByQuizQuestionId(ByVal quizQuestionId As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "Select * From Quizes_Questions Where Quiz_QuestionId=@f1"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", quizQuestionId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectPossibleAnswersByQuizQuestionId(ByVal quizQuestionId As Int32) As OleDbDataReader

        Try

        
        myConnection.Open()
        Dim SqlString As String = "Select Quiz_QuestionPossibleAnswer1,Quiz_QuestionPossibleAnswer2,Quiz_QuestionPossibleAnswer3,Quiz_QuestionPossibleAnswer4 From Quizes_Questions Where Quiz_QuestionId=@f1"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", quizQuestionId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function SelectQuiz_QuestionByQuizId(ByVal quizId As Int32) As OleDbDataReader
        Try


            myConnection.Open()
            Dim SqlString As String = "Select * From Quizes_Questions Where QuizId=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", quizId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub UpdateQuizQuestionByQuizQuestionIDWithPicture(ByVal QuizQuestionId As Int32, ByVal Content As String, ByVal PossibleAnswer1 As String, ByVal PossibleAnswer2 As String, ByVal PossibleAnswer3 As String, ByVal PossibleAnswer4 As String, ByVal CorrectAnswer As String, ByVal PictureUrl As String, ByVal AnswerDescription As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "UPDATE   Quizes_Questions Set Quiz_QuestionContent =@f1, Quiz_QuestionPossibleAnswer1 =@f2, Quiz_QuestionPossibleAnswer2 =@f3, Quiz_QuestionPossibleAnswer3 =@f4, Quiz_QuestionPossibleAnswer4 =@f5, Quiz_QuestionCorrectAnswer = @f6, Quiz_QuestionAnswerDescription =@f7, Quiz_QuestionPictureUrl =@f8 where Quiz_QuestionId = @f9"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", Content)
            newCmd.Parameters.AddWithValue("@f2", PossibleAnswer1)
            newCmd.Parameters.AddWithValue("@f3", PossibleAnswer2)
            newCmd.Parameters.AddWithValue("@f4", PossibleAnswer3)
            newCmd.Parameters.AddWithValue("@f5", PossibleAnswer4)
            newCmd.Parameters.AddWithValue("@f6", CorrectAnswer)
            newCmd.Parameters.AddWithValue("@f7", AnswerDescription)
            newCmd.Parameters.AddWithValue("@f8", PictureUrl)
            newCmd.Parameters.AddWithValue("@f9", QuizQuestionId)

            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub UpdateQuizQuestionByQuizQuestionIDWithoutPicture(ByVal QuizQuestionId As Int32, ByVal Content As String, ByVal PossibleAnswer1 As String, ByVal PossibleAnswer2 As String, ByVal PossibleAnswer3 As String, ByVal PossibleAnswer4 As String, ByVal CorrectAnswer As String, ByVal AnswerDescription As String)
        Try

        
        myConnection.Open()
        Dim SqlString As String = "UPDATE   Quizes_Questions Set Quiz_QuestionContent =@f1, Quiz_QuestionPossibleAnswer1 =@f2, Quiz_QuestionPossibleAnswer2 =@f3, Quiz_QuestionPossibleAnswer3 =@f4, Quiz_QuestionPossibleAnswer4 =@f5, Quiz_QuestionCorrectAnswer = @f6, Quiz_QuestionAnswerDescription =@f7 where Quiz_QuestionId = @f8"
        Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        newCmd.CommandType = CommandType.Text
        newCmd.Parameters.AddWithValue("@f1", Content)
        newCmd.Parameters.AddWithValue("@f2", PossibleAnswer1)
        newCmd.Parameters.AddWithValue("@f3", PossibleAnswer2)
        newCmd.Parameters.AddWithValue("@f4", PossibleAnswer3)
        newCmd.Parameters.AddWithValue("@f5", PossibleAnswer4)
        newCmd.Parameters.AddWithValue("@f6", CorrectAnswer)
        newCmd.Parameters.AddWithValue("@f7", AnswerDescription)
        newCmd.Parameters.AddWithValue("@f8", QuizQuestionId)

        newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

End Class
